using System;
using System.Collections.Generic;
using System.Text;
using DTOLayer;
using System.Collections;
using System.Data.OleDb;
using System.Data;


namespace BUSLayer
{
    public class LoaiTietKiemDAO
    {
        public static void ThemLoaiTietKiem(LoaiTietKiemDTO ltk)
        {
            //B1&B2 Tao chuoi ket noi va ket noi bang doi tuong ket noi
            OleDbConnection cn;
            cn = Database.ConnectionData();
            //B3 Tao chuoi strSQL ket noi CSDL
            string strSQL = "";
            //strSQL = @" Insert into NHANVIEN(MANV,HO_TEN,DIA_CHI,DIEN_THOAI,MA_NQL,MA_PHG)" +
            //        "Values ('" +nvdto .MaNV +"','" +nvdto .HoTen +"','"+nvdto .DiaChi +"','"+
            //        nvdto .DienThoai +"','"+nvdto .MaNQL +"','"+ nvdto .MaPHG +"')";

            strSQL = "Insert Into LOAITIETKIEM(MaLoaiTietKiem,TenLoaiTietKiem,LaiSuat,KyHan,ThoiHanRutToiThieu) Values(?,?,?,?,?)";
            //B4 Thuc Thi chuoi strSQL
            OleDbCommand cmd = new OleDbCommand(strSQL, cn);

            cmd.Parameters.Add("@MaLoaiTietKiem", OleDbType.LongVarChar);
            cmd.Parameters.Add("@TenLoaiTietKiem", OleDbType.VarChar);
            cmd.Parameters.Add("@LaiSuat", OleDbType.Double);
            cmd.Parameters.Add("@KyHan", OleDbType.Double);
            cmd.Parameters.Add("@ThoiHanRutToiThieu", OleDbType.LongVarChar);

            cmd.Parameters[0].Value = ltk.MaLoaiTK;
            cmd.Parameters[1].Value = ltk.TenLoaiTK;
            cmd.Parameters[2].Value = ltk.LaiSuat;
            cmd.Parameters[3].Value = ltk.KyHan;
            cmd.Parameters[4].Value = ltk.ThoiHanRutToiThieu;


            cmd.ExecuteNonQuery();

            //B5Dong ket noi
            cn.Close();
        }
        public static void DeleteLoaiTietKiemTheoMaLoaiTietKiem(long maltk)
        {
            //B1 &B2 Tao chuoi ket noi va mo doi tuong ket noi bang doi tuong ket noi
            OleDbConnection cn;
            cn = Database.ConnectionData();
            //B2 Tao chuoi strSQL Thao Tac CSDL
            string strSQl = "";
            strSQl = "Delete From LOAITIETKIEM Where MaLoaiTietKiem= ?";
            //B4 Thuc thi chuoi strSQL
            OleDbCommand cmd = new OleDbCommand(strSQl, cn);

            cmd.Parameters.Add("@MaLoaiTietKiem", OleDbType.LongVarChar);
            cmd.Parameters["@MaLoaiTietKIem"].Value = maltk;
            cmd.ExecuteNonQuery();

            //B5 dong ket noi
            cn.Close();
        }
        public static long MaxMaLoaiTietKiem()
        {
            //B1&B2 tao chuoi kiet noi
            // OleDbConnection cn;
            // cn = Database.ConnectionData();
            //B3 Tao chuoi ket noi CSDL
            string sql = "SELECT  Max(MaLoaiTietKiem) From LOAITIETKIEM";
            //B4 thuc thi chuoi sql
            // OleDbCommand cmd = new OleDbCommand(sql, cn);
            long kq;
            // kq= long.Parse( cmd.ExecuteScalar().ToString());
            //cn.Close();

            kq = Database.ExecuteScalar(sql);
            return kq;

        }
        public static List<LoaiTietKiemDTO> SelectLoaiTietKiemALL()
        {
            try
            {

                List<LoaiTietKiemDTO> list = new List<LoaiTietKiemDTO>();
                //B1&B2 tao chuoi ket noi
                OleDbConnection cn;
                cn = Database.ConnectionData();
                //B3 tao chuoi ket noi CSDL
                string sql = "SELECT * From LOAITIETKIEM";
                //b4 thuc thi chuoi ket noi sql
                OleDbCommand cmd = new OleDbCommand(sql, cn);
                OleDbDataReader dr;
                dr = cmd.ExecuteReader();
                while (dr.Read())
                {
                    LoaiTietKiemDTO ltkdto = new LoaiTietKiemDTO();
                    ltkdto.MaLoaiTK = long.Parse(dr["MaLoaiTietKiem"].ToString());
                    ltkdto.TenLoaiTK = (string)dr["TenLoaiTietKiem"];
                    ltkdto.LaiSuat = (double)dr["LaiSuat"];
                    ltkdto.KyHan = (double)dr["KyHan"];
                    ltkdto.ThoiHanRutToiThieu = long.Parse(dr["ThoiHanRutToiThieu"].ToString());
                    list.Add(ltkdto);

                }
                cn.Close();
                return list;

            }
            catch (Exception ex)
            {
                throw ex;
            }

        }
        public static void UpdateLoaiTietKeim(LoaiTietKiemDTO ltk)
        {
            //B1&B2 Tao chuoi ket noi va mo ket noi bang doi tuong ket noi
            OleDbConnection cn;
            cn = Database.ConnectionData();
            //B3 Tao chuoi strSQL thao tac CSDL
            string strSQL;

            strSQL = "UPDATE LOAITIETKIEM Set TenLoaiTietKiem=? ,LaiSuat=? ,KyHan=? ,ThoiHanRutToiThieu=? Where MaLoaiTietKiem=?";
            //strSQL = " Update AccountNV Set UserName = '" + nvdto.Name + " ',Password= '" + nvdto.Pass + "' Where MaNV = '" + nvdto.MaNV + "' ";

            //B4 Thuc Thi chuoi strSQL
            OleDbCommand cmd = new OleDbCommand(strSQL, cn);

            cmd.Parameters.Add("@TenLoaiTietKiem", OleDbType.VarChar);
            cmd.Parameters.Add("@LaiSuat", OleDbType.Double);
            cmd.Parameters.Add("@KyHan", OleDbType.Double);
            cmd.Parameters.Add("@ThoiHanRutToiThieu", OleDbType.LongVarChar);
            cmd.Parameters.Add("@MaLoaiTietKiem", OleDbType.LongVarChar);

            cmd.Parameters[0].Value = ltk.TenLoaiTK;
            cmd.Parameters[1].Value = ltk.LaiSuat;
            cmd.Parameters[2].Value = ltk.KyHan;
            cmd.Parameters[3].Value = ltk.ThoiHanRutToiThieu;
            cmd.Parameters[4].Value = ltk.MaLoaiTK;

            cmd.ExecuteNonQuery();
            //B5 Dong ket noi
            cn.Close();

        }
        public static DataTable FindLoaiTietKiem(long ma, string ten)
        {
            DataTable dt = new DataTable();
            try
            {

                OleDbConnection cn;
                cn = Database.ConnectionData();
                string sql = "Select * From LOAITIETKIEM Where TenLoaiTietKiem =' " + ten + "% '";
                if (ma != 0)
                {
                    sql += "MaLoaiTietKiem=" + ma.ToString();
                }
                // OleDbCommand cmd = new OleDbCommand(sql, cn);
                OleDbDataAdapter da;
                da = new OleDbDataAdapter(sql, cn);

                da.Fill(dt);
                cn.Close();

            }
            catch
            {
            }
            return dt;

        }
        public static DataTable SelectALLLoaiTietKiemDataTabe()
        {
            DataTable dt = new DataTable();
           // try
            {

                OleDbConnection cn;
                cn = Database.ConnectionData();
                string sql = "Select * From LOAITIETKIEM";

                OleDbDataAdapter da;
                da = new OleDbDataAdapter(sql, cn);

                da.Fill(dt);
                cn.Close();

            }
           // catch
            {
            }
            return dt;

        }
        public static List<LoaiTietKiemDTO> ListFindLoaiTietKiem(long ma, string ten)
        {
            List<LoaiTietKiemDTO> list = new List<LoaiTietKiemDTO>();
            try
            {

                OleDbConnection cn;
                cn = Database.ConnectionData();
                //string sql = "SELECT * From LOAITIETKIEM Where TenLoaiTietKiem Like 'e%'";
                string sql = "Select * From LOAITIETKIEM Where TenLoaiTietKiem Like '" + ten + "%'";
                if (ma != 0)
                {
                    sql += "MaLoaiTietKiem=" + ma.ToString();
                }
                // OleDbCommand cmd = new OleDbCommand(sql, cn);
                OleDbCommand cmd = new OleDbCommand(sql, cn);
                OleDbDataReader dr;
                dr = cmd.ExecuteReader();
                while (dr.Read())
                {
                    LoaiTietKiemDTO ltkdto = new LoaiTietKiemDTO();
                    ltkdto.MaLoaiTK = long.Parse(dr["MaLoaiTietKiem"].ToString());
                    ltkdto.TenLoaiTK = (string)dr["TenLoaiTietKiem"];
                    ltkdto.LaiSuat = (double)dr["LaiSuat"];
                    ltkdto.KyHan = (double)dr["KyHan"];
                    ltkdto.ThoiHanRutToiThieu = long.Parse(dr["ThoiHanRutToiThieu"].ToString());
                    list.Add(ltkdto);
                    

                }
                cn.Close();
                return list;

               

            }
            catch
            {
            }
            return list;

        }
        public static LoaiTietKiemDTO LayLoaiTietKiemTheoMaLTK(long maltk)
        {
            LoaiTietKiemDTO ltkdto = new LoaiTietKiemDTO();
            //B1 & B2: Tao chuoi ket noi, mo ket noi bang doi tuong ket noi
            OleDbConnection cn;
            cn = Database.ConnectionData();

            //B3: Tao chuoi strSQL thao tac CSDL
            string strSQL;
            strSQL = "Select * From LOAITIETKIEM Where MaLoaiTietKiem= " + maltk.ToString() + "";

            //B4: Thuc thi chuoi strSQL
            OleDbCommand cmd = new OleDbCommand(strSQL, cn);
            OleDbDataReader dr;
            dr = cmd.ExecuteReader();
            while (dr.Read())
            {
                ltkdto.MaLoaiTK = long.Parse(dr["MaLoaiTietKiem"].ToString());
                ltkdto.TenLoaiTK = (string)dr["TenLoaiTietKiem"];
                ltkdto.LaiSuat = (double)dr["LaiSuat"];
                ltkdto.KyHan = (double)dr["KyHan"];
                ltkdto.ThoiHanRutToiThieu = long.Parse(dr["ThoiHanRutToiThieu"].ToString());

            }
            //B5: Dong ket noi CSDL

            cn.Close();
            return ltkdto;
        }


    }
}
